package com.cyan.teamquestionnaire.mappers;

import com.cyan.teamquestionnaire.entity.AnswerSheet;
import com.cyan.teamquestionnaire.entity.Questionnaire;
import com.cyan.teamquestionnaire.entity.QuestionnaireFee;
import com.cyan.teamquestionnaire.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface QuestionnaireMapper {

    // 通过title模糊查询, 姓名准确查询
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where title like #{title} and name = #{Cname} and status != 3")
    List<Questionnaire> queryQuestionnaireByTitle(@Param(value = "title") String title, @Param(value = "Cname") String Cname);

    // 通过qid模糊查询
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where qid like #{qid}")
    List<Questionnaire> queryQuestionnaireByQid(String qid);

    // 通过qid精确查询查询
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where qid = #{qid} and status != 3")
    Questionnaire queryQuestionnaireByQidEq(String qid);

    // 通过qid精确查询查询,回收站的
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where qid = #{qid} and status = 3")
    Questionnaire queryQuestionnaireByQidEqFromBin(String qid);

    //通过qid精确查询，同时title要和传入的title进行模糊查询
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where qid = #{qid} and title like #{title}")
    Questionnaire queryQuestionnaireByQidTitle(@Param(value = "qid") String qid, @Param(value = "title") String title);


    // 通过name模糊查询
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where name like #{name}")
    List<Questionnaire> queryQuestionnaireByName(String name);

    // 查询模板
    @Select("select * from questionnaire where ISNULL(name) and title like #{title}")
    List<Questionnaire> queryTemplate(String title);

    // 查看某一用户创建的所有问卷
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where name = #{name} and status != 3")
    List<Questionnaire> selectByCreatorName(String name);

    // 批量查看问卷-发布和暂停的
    @Select("select * from questionnaire where name = #{name} and status != 0 and status != 3")
    List<Questionnaire> selectQuestionnaire12(String name);

    // 创建问卷时不设置发布，截止，删除等时间
    @Insert("insert into questionnaire(qid, name, title, content) values(#{qId}, #{name}, #{title}, #{content})")
    void insertQuestionnaire(Questionnaire questionnaire);

    /**
     * @param qId 通过qId删除，真正的删除操作，相当于从回收站删除
     * @return 影响行数
     */
    @Delete("delete from questionnaire where qid = #{qId}")
    int deleteQuestionnaire(String qId);

    /**
     * @param questionnaire 要删除的问卷，假删除，仅仅修改状态
     * @return 影响行数
     */
    @Update("update questionnaire set status = 3, delete_time = #{deleteTime} where qid = #{qId}")
    int putQuestionnaireToBin(Questionnaire questionnaire);

    /**
     * @param name，query 删除在回收站的问卷，根据name和status查询,同时对title进行模糊查询
     * @return 影响行数
     */
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where name = #{name} and status = 3 and title like #{query} ")
    List<Questionnaire> getQuestionnaireFromBin(@Param(value = "name") String name, @Param(value = "query") String query);


    // 第一次发布问卷，更新问卷状态和发布时间以及群组
    @Update("update questionnaire set `status` = 1, release_time = #{release} ,gid = #{gId} where qid = #{qId}")
    void issue(@Param(value = "qId") String qId, @Param(value = "release") String release, @Param(value = "gId") String gId);


    // 修改问卷状态，相当于关闭和打开问卷
    @Update("update questionnaire set status = #{status} where qid = #{qId}")
    void updateStatus(@Param(value = "qId") String qId, @Param(value = "status") String status);

    /**
     * 满足功能：单卷查看答卷，为了解耦，将问卷和答案分开
     * 在后端Controller需要另查询相应问卷
     *
     * @param answerSheet 传入的参数没有内容
     * @return 答卷对象
     */
    @Results({
            @Result(column = "user_name", property = "name"),
            @Result(column = "answer", property = "answer")
    })
    @Select("select * from answer_sheet where user_name = #{name} and qid =#{qId} limit 1")
    AnswerSheet selectAnsweredSheet(AnswerSheet answerSheet);

    // 批量查看答卷
    @Select("select * from answer_sheet as a where a.qid = #{qId}")
    List<AnswerSheet> selectAnswers(String qId);

    // 修改问卷内容,修改content和title
    @Update("update questionnaire set content = #{content}, title = #{title} where qid = #{qId}")
    int updateQuestionnaireContent(Questionnaire questionnaire);

    /**
     * 某个租户查询所有问卷的收费情况
     *
     * @param user 租户
     * @return 所有问卷收费情况
     */
    @Select("select r.qid, count(user_name) as cnt from " +
            "answer_sheet as a, " +
            "(select q.qid from questionnaire as q, groups as g where g.tenant_name = #{name} and g.gid = q.gid) as r " +
            "where a.qid = r.qid group by r.qid;")
    List<QuestionnaireFee> getFeeOfTenant(User user);

    // 查看一个租户的群组个数
    @Select("select count(g.gid) as cnt from groups as g where g.tenant_name = #{name}")
    int groupCnt(User u);

    //根据一个gid查询一个群组里面的问卷
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where gid = #{gId} ")
    List<Questionnaire> getQuestionnaireByGid (String gId);


    // 查询租户下所有问卷数
    @Select("select count(q.qid) from questionnaire as q , \n" +
            "(select gid from groups where tenant_name = #{name}) as g\n" +
            " where q.gid = g.gid")
    int countQuestionnaireTenant(String name);

    // 查看某一用户创建的所有问卷，在发布中以及暂停的
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where name = #{name} and status != 3 and status != 0 and title like #{query}")
    List<Questionnaire> selectByCreatorNameStatus(@Param(value = "name") String name, @Param(value = "query") String query);

    //判断发布时间在今天的数量
    @Results({
            @Result(column = "release_time", property = "releaseTime"),
            @Result(column = "delete_time", property = "deleteTime")
    })
    @Select("select * from questionnaire where  name = #{name} and release_time between  #{Morning} and #{Evening}")
    List<Questionnaire> releaseTimeInToday(@Param(value = "Morning")String Morning,@Param(value = "Evening")String Evening,@Param(value = "name")String name);
}
